var express = require('express');
var router = express.Router();
var db = require('../public/javascripts/db');
var async = require('async');
var token = require('../public/javascripts/utils');

function transfer(date) {
    // console.log('timememmemmemem');
    var time = new Date(date);
    var newTime = time.getFullYear() + '-' + (time.getMonth() + 1) + '-' + time.getDate() + ' ' +
        time.getHours() + ':' + time.getMinutes() + ':' + time.getSeconds();
    return newTime;
}

var compare = function (obj1, obj2) {
    var val1 = obj1.time;
    var val2 = obj2.time;
    if (val1 < val2) {
        return 1;
    } else if (val1 > val2) {
        return -1;
    } else {
        return 0;
    }
}

router.get('/', (req, res, next) => {
    console.log(req.query)
    if (req.query.num && req.query.time[0]) {
        var sql = 'SELECT * FROM users WHERE identify_num = ? AND user_id = ?';
        var sqlParams = [req.query.num, token.verifyToken(req.headers.token)];
        db.query(sql, sqlParams, (err, result) => {
            if (err) {
                throw err;
                return;
            } else {
                //async解决循环异步查询数据库
                // console.log('result', result);

                async.map(result, function (data1, callback1) {
                    var sql2 = 'SELECT * FROM images WHERE owner_id = ?';
                    db.query(sql2, data1.id, function (err2, result2) {
                        async.map(result2, function (data2, callback2) {
                            var sql3 = 'SELECT * FROM results WHERE img_id = ?';
                            db.query(sql3, data2.id, function (err3, result3) {
                                data2.id = result3[0].id;
                                data2.img_id = data2.id;
                                data2.isDr = result3[0].isDr;
                                data2.mass_vessel = result3[0].mass_vessel;
                                data2.odlocation = result3[0].odlocation;
                                data2.number = data1.identify_num;
                                data2.owner = data1.name;
                                data2.owner_id = data1.id;
                                data2.time = result3[0].created_time;
                                data2.url = data2.location;
                                data2.user_id = data2.load_by;
                                data2.vessel = result3[0].vessel;

                                // array.push(data2)
                                // console.log('data2', data2)
                                callback2(null, data2)
                            })
                            // callback2(null,data2)
                        }, function (err, result) {
                            // console.log('result', result)
                            callback1(null, result)
                        })
                    })
                }, function (err, finalresult) {
                    // console.log('finalresult', finalresult)
                    // console.log('finalresult[0][0]', finalresult[0][0])
                    var data = [];
                    var newData = [];
                    for (var i = 0; i < finalresult.length; i++) {
                        for (var j = 0; j < finalresult[i].length; j++) {
                            data.push(finalresult[i][j])
                        }
                    }
                    data.forEach((value, index, array) => {
                        console.log('value', value.time)
                        if (value.time >= req.query.time[0] && value.time <= (req.query.time[1] + ' 23:59:59')) {
                            newData.push(value)
                        }

                    })
                    newData.sort(compare)
                    // console.log('finaldata', data)
                    res.send({status: 2, data: newData})
                })

                // var sql2 = 'SELECT * FROM images WHERE owner_id = ?';
                // db.query(sql2, result[0].id, (err2, result2) => {
                //     async.each(result2, (item, callback) => {
                //         // console.log('item', item);
                //         var sql3 = 'SELECT * FROM results WHERE img_id = ?';
                //         db.query(sql3, item.id, (err3, result3) => {
                //             // console.log('result2', result2);
                //             item.id = result3[0].id;
                //             item.img_id = item.id;
                //             item.isDr = result3[0].isDr;
                //             // item.load_by =
                //             item.mass_vessel = result3[0].mass_vessel;
                //             item.odlocation = result3[0].odlocation;
                //             item.number = result[0].identify_num;
                //             item.owner = result[0].name;
                //             item.owner_id = result[0].id;
                //             item.time = result3[0].created_time;
                //             item.url = item.location;
                //             item.user_id = item.load_by;
                //             item.vessel = result3[0].vessel;
                //
                //             callback(null)
                //         })
                //     }, () => {
                //         console.log('final result', result2);
                //         var data = []
                //         result2.forEach((value, index, array) => {
                //             console.log('value', value.time)
                //             // console.log('index', index)
                //             // console.log('array', array)
                //             // if (value.time < req.query.time[0] || value.time > req.query.time[1]) {
                //             //     console.log('time value', value);
                //             //     result2.splice(index, 1)
                //             // }
                //             if (value.time >= req.query.time[0] && value.time <= req.query.time[1]) {
                //                 data.push(value)
                //             }
                //
                //         })
                //         res.send({status: 2, data: data})
                //     })
                // })
            }
        })
    }
    else if (req.query.num) {
        var sql = 'SELECT * FROM users WHERE identify_num = ? AND user_id = ?';
        var sqlParams = [req.query.num, token.verifyToken(req.headers.token)];
        db.query(sql, sqlParams, (err, result) => {
            if (err) {
                throw err;
                return;
            } else {
                //async解决循环异步查询数据库
                // console.log('result', result);

                var array = []
                async.map(result, function (data1, callback1) {
                    var sql2 = 'SELECT * FROM images WHERE owner_id = ?';
                    db.query(sql2, data1.id, function (err2, result2) {
                        async.map(result2, function (data2, callback2) {
                            var sql3 = 'SELECT * FROM results WHERE img_id = ?';
                            db.query(sql3, data2.id, function (err3, result3) {
                                data2.id = result3[0].id;
                                data2.img_id = data2.id;
                                data2.isDr = result3[0].isDr;
                                data2.mass_vessel = result3[0].mass_vessel;
                                data2.odlocation = result3[0].odlocation;
                                data2.number = data1.identify_num;
                                data2.owner = data1.name;
                                data2.owner_id = data1.id;
                                data2.time = result3[0].created_time;
                                data2.url = data2.location;
                                data2.user_id = data2.load_by;
                                data2.vessel = result3[0].vessel;

                                // array.push(data2)
                                // console.log('data2', data2)
                                callback2(null, data2)
                            })
                            // callback2(null,data2)
                        }, function (err, result) {
                            console.log('result', result)
                            callback1(null, result)
                        })
                    })
                }, function (err, finalresult) {
                    // console.log('finalresult', finalresult)
                    // console.log('finalresult[0][0]', finalresult[0][0])
                    var data = [];
                    for (var i = 0; i < finalresult.length; i++) {
                        for (var j = 0; j < finalresult[i].length; j++) {
                            data.push(finalresult[i][j])
                        }
                    }
                    data.sort(compare)
                    // console.log('finaldata', data)
                    res.send({status: 2, data: data})
                })


                // var sql2 = 'SELECT * FROM images WHERE owner_id = ?';
                // db.query(sql2, result[0].id, (err2, result2) => {
                //     async.each(result2, (item, callback) => {
                //         // console.log('item', item);
                //         var sql3 = 'SELECT * FROM results WHERE img_id = ?';
                //         db.query(sql3, item.id, (err3, result3) => {
                //             // console.log('result2', result2);
                //             item.id = result3[0].id;
                //             item.img_id = item.id;
                //             item.isDr = result3[0].isDr;
                //             // item.load_by =
                //             item.mass_vessel = result3[0].mass_vessel;
                //             item.odlocation = result3[0].odlocation;
                //             item.number = result[0].identify_num;
                //             item.owner = result[0].name;
                //             item.owner_id = result[0].id;
                //             item.time = result3[0].created_time;
                //             item.url = item.location;
                //             item.user_id = item.load_by;
                //             item.vessel = result3[0].vessel;
                //
                //             console.log('item', item)
                //
                //             callback(null)
                //         })
                //     }, () => {
                //         // console.log('final result', result);
                //         res.send({status: 2, data: result2})
                //     })
                // })
                // async.each(result, (item, callback) => {
                //     console.log(item)
                //     var sql2 = 'SELECT * FROM images WHERE owner_id = ?';
                //     db.query(sql2, item.id, (err2, result2) => {
                //         if (err2) {
                //             throw err2;
                //             return;
                //         } else {
                //             var sql3 = 'SELECT * FROM results WHERE owner_id = ?';
                //             db.query(sql3, item.id, (err3, result3) => {
                //                 // console.log('result2', result2);
                //                 item.url = result2[0].location;
                //                 item.type = result2[0].type;
                //                 item.time = result2[0].load_time;
                //                 item.load_by = result2[0].load_by;
                //                 item.owner = result3[0].name;
                //                 item.number = result3[0].identify_num;
                //                 callback(null)
                //             })
                //         }
                //     })
                // }, () => {
                //     // console.log('final result', result);
                //     res.send({status: 2, data: result})
                // })
            }
        })
    }
    else if (req.query.time[0]) {
        // console.log(req.query.time[0])
        // console.log(req.query.time[1])
        // var sql = `SELECT * FROM results WHERE created_time BETWEEN ${req.query.time[0]} AND
        // ${req.query.time[1]} ORDER BY created_time DESC`;
        var sql = `SELECT * FROM results WHERE created_time between ${"'" + req.query.time[0] + "'"} and 
        ${"'" + req.query.time[1] + ' 23:59:59' + "'"} AND user_id = ? ORDER BY created_time DESC`;
        // console.log('sql', sql)
        db.query(sql, token.verifyToken(req.headers.token), (err, result) => {
            if (err) {
                throw err;
                return;
            } else {
                // console.log('time result', result)
                async.each(result, (item, callback) => {
                    // console.log('时间排序',item)
                    var sql2 = 'SELECT * FROM images WHERE id = ?';
                    db.query(sql2, item.img_id, (err2, result2) => {
                        if (err2) {
                            throw err2;
                            return;
                        } else {
                            var sql3 = 'SELECT * FROM users WHERE id = ?';
                            db.query(sql3, item.owner_id, (err3, result3) => {
                                // console.log('result2', result2);
                                item.url = result2[0].location;
                                item.type = result2[0].type;
                                item.time = item.created_time;
                                item.load_by = result2[0].load_by;
                                item.owner = result3[0].name;
                                item.number = result3[0].identify_num;
                                callback(null)
                            })
                        }
                    })
                }, () => {
                    // console.log('final result', result);
                    res.send({status: 2, data: result})
                })
            }
        })

    }
    else {
        var sql = 'SELECT * FROM results WHERE user_id = ? ORDER BY id DESC';
        db.query(sql, token.verifyToken(req.headers.token), (err, result) => {
            if (err) {
                throw err;
                return;
            } else {
                // console.log('result', result);
                // result.forEach((item) => {
                //async解决循环异步查询数据库
                async.each(result, (item, callback) => {
                    // console.log('时间排序',item)
                    var sql2 = 'SELECT * FROM images WHERE id = ?';
                    db.query(sql2, item.img_id, (err2, result2) => {
                        if (err2) {
                            throw err2;
                            return;
                        } else {
                            var sql3 = 'SELECT * FROM users WHERE id = ?';
                            db.query(sql3, item.owner_id, (err3, result3) => {
                                // console.log('result2', result2);
                                item.url = result2[0].location;
                                item.type = result2[0].type;
                                item.time = item.created_time;
                                item.load_by = result2[0].load_by;
                                item.owner = result3[0].name;
                                item.number = result3[0].identify_num;
                                callback(null)
                            })
                        }
                    })
                }, () => {
                    // console.log('final result', result);
                    // console.log(transfer('2018-09-28T02:59:07.000Z'));
                    res.send({status: 2, data: result})
                })

                // async.each(terraceList, function (item, callback) {
                //     Source.count({terrace: item}, (err, data) => {
                //         if (err) throw err
                //         terraceCount.push(data)
                //         callback(null)
                //     })
                // }, function (err) {
                //     console.log(err)
                //     result.data = terraceCount
                //     res.json(result)
                // })


            }
        })
    }
})

module.exports = router;